from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, DateTime, Text
from sqlalchemy.orm import sessionmaker, relationship, declarative_base
from config import DATABASE_URL
import logging
from datetime import datetime
from sqlalchemy.exc import SQLAlchemyError

# 设置日志
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)
ch = logging.StreamHandler()
ch.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
logger.addHandler(ch)

# 创建Base类
Base = declarative_base()

# 数据库连接
engine = create_engine(DATABASE_URL)

# 创建Session类
Session = sessionmaker(bind=engine)
session = Session()

# 定义Quote表结构
class Quote(Base):
    __tablename__ = 'quotes'

    id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(String, nullable=False)
    author = Column(String, nullable=True)
    source = Column(String, nullable=True)
    uuid = Column(String, unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f"<Quote(id={self.id}, content={self.content}, author={self.author}, source={self.source})>"

# 定义Plot表结构
class Plot(Base):
    __tablename__ = 'plots'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    content = Column(Text, nullable=False)  # 确保使用了 Text 类型以支持较长文本
    previous_chapter_id = Column(Integer, ForeignKey('plots.id'))
    created_at = Column(DateTime, nullable=True)

    previous_chapter = relationship('Plot', remote_side=[id])

    def __repr__(self):
        return f"<Plot(title={self.title}, id={self.id})>"

# 创建数据库表（如果还不存在）
Base.metadata.create_all(engine)

# 插入Quote数据
def insert_quote(quotes_table, data):
    try:
        # 创建一个新的引用实例
        new_quote = quotes_table(
            content=data['content'],
            author=data.get('author', 'Unknown'),
            source=data.get('source', 'Unknown'),
            uuid=data['uuid'],
            created_at=data['created_at'] or datetime.utcnow()
        )

        # 将数据添加到会话并提交
        session.add(new_quote)
        session.commit()
        logger.info(f"Quote inserted successfully: {new_quote}")

    except SQLAlchemyError as e:
        session.rollback()  # 发生错误时回滚
        logger.error(f"Error inserting quote: {e}")
    except Exception as e:
        session.rollback()
        logger.error(f"Unexpected error while inserting quote: {e}")


# 插入Plot数据
def insert_plot(title, content, previous_chapter_id=None, created_at=None):
    try:
        # 你可以检查 content 的长度，避免超出限制
        max_content_length = 65535  # MySQL LONGTEXT 的最大长度
        if len(content) > max_content_length:
            logger.error("Content too long to insert into database.")
            return None

        # 插入数据到数据库
        new_plot = Plot(title=title, content=content, previous_chapter_id=previous_chapter_id, created_at=created_at)
        session.add(new_plot)
        session.commit()
        logger.info(f"Plot '{title}' inserted into database.")
    except Exception as e:
        session.rollback()
        logger.error(f"Error inserting plot: {e}")
        return None

# 获取Quote表
def fetch_quotes_table():
    """
    获取用于存储引用的数据库表。
    """
    return Quote

# 获取Plot表
def fetch_plots_table():
    """
    获取用于存储情节的数据库表。
    """
    return Plot

# 调用示例
if __name__ == "__main__":
    # 假设我们从某个地方获取到的引用数据
    example_quote_data = {
        'content': "生活不止眼前的苟且，还有诗和远方。",
        'author': "王小波",
        'source': "黄金时代",
        'uuid': "1234567890",
        'created_at': datetime.now()
    }

    insert_quote(example_quote_data)

    # 假设我们从某个地方获取到的情节数据
    example_plot_data = {
        'title': "Chapter 1",
        'content': "This is the content of chapter 1. It's a placeholder content to demonstrate plot insertion.",
        'previous_chapter_id': None,
        'created_at': datetime.now()
    }

    insert_plot(example_plot_data['title'], example_plot_data['content'], example_plot_data['previous_chapter_id'], example_plot_data['created_at'])
